package com.example.service;

import com.example.mapper.DbConnectionMapper;
import com.example.model.DbConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.encrypt.BytesEncryptor;
import org.springframework.security.crypto.encrypt.Encryptors;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Base64;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class DbConnectionService {
    private static final Logger log = LoggerFactory.getLogger(DbConnectionService.class);
    
    private static final String ENCRYPTION_PASSWORD = "4af#ksl@qvty7&4x"; // 实际应用中应从配置获取
    private static final String ENCRYPTION_SALT = "92ae31af88d65a13"; // 实际应用中应从配置获取

    @Autowired
    private DbConnectionMapper dbConnectionMapper;

    // 加密处理
    private BytesEncryptor encryptor = Encryptors.standard(ENCRYPTION_PASSWORD, ENCRYPTION_SALT);

    /**
     * 获取用户的所有数据库连接（不包含密码明文）
     */
    public List<DbConnection> getUserConnections(Long userId) {
        List<DbConnection> connections = dbConnectionMapper.findByUserId(userId);
        // 返回无密码版本
        return connections.stream()
                .map(DbConnection::withoutPassword)
                .collect(Collectors.toList());
    }
    
    /**
     * 获取所有用户的数据库连接（供管理员使用，不包含密码明文）
     */
    public List<DbConnection> getAllConnections() {
        List<DbConnection> connections = dbConnectionMapper.findAll();
        // 返回无密码版本
        return connections;
    }
    
    /**
     * 分页获取所有数据库连接（供管理员使用）
     * @param page 页码（从1开始）
     * @param pageSize 每页记录数
     * @return 分页后的连接列表
     */
    public List<DbConnection> getPagedConnections(int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        return dbConnectionMapper.findAllPaged(offset, pageSize);
    }
    
    /**
     * 分页获取指定用户的数据库连接
     * @param userId 用户ID
     * @param page 页码（从1开始）
     * @param pageSize 每页记录数
     * @return 分页后的连接列表
     */
    public List<DbConnection> getPagedUserConnections(Long userId, int page, int pageSize) {
        int offset = (page - 1) * pageSize;
        List<DbConnection> connections = dbConnectionMapper.findByUserIdPaged(userId, offset, pageSize);
        // 返回无密码版本
        return connections.stream()
                .map(DbConnection::withoutPassword)
                .collect(Collectors.toList());
    }
    
    /**
     * 获取指定用户的连接总数
     * @param userId 用户ID
     * @return 连接总数
     */
    public int getUserConnectionCount(Long userId) {
        List<DbConnection> connections = dbConnectionMapper.findByUserId(userId);
        return connections.size();
    }
    
    /**
     * 获取数据库连接总数
     */
    public int getTotalConnectionCount() {
        return dbConnectionMapper.count();
    }

    /**
     * 获取用户的默认数据库连接
     */
    public DbConnection getDefaultConnection(Long userId) {
        DbConnection connection = dbConnectionMapper.findDefaultByUserId(userId);
        if (connection != null) {
            // 解密密码
            connection.setPassword(decryptPassword(connection.getPassword()));
            return connection;
        }
        return null;
    }

    /**
     * 根据ID获取数据库连接（含解密后的密码）
     */
    public DbConnection getConnectionById(Long id, Long userId) {
        DbConnection connection = dbConnectionMapper.findById(id);
        
        // 验证所有权
        if (connection == null || !connection.getUserId().equals(userId)) {
            throw new RuntimeException("数据库连接不存在或无访问权限");
        }
        
        // 解密密码
        connection.setPassword(decryptPassword(connection.getPassword()));
        return connection;
    }
    
    /**
     * 管理员访问：根据ID获取数据库连接（含解密后的密码）
     */
    public DbConnection getConnectionByIdForAdmin(Long id) {
        DbConnection connection = dbConnectionMapper.findById(id);
        
        if (connection == null) {
            throw new RuntimeException("数据库连接不存在");
        }
        
        // 解密密码
        connection.setPassword(decryptPassword(connection.getPassword()));
        return connection;
    }

    /**
     * 创建新的数据库连接
     */
    @Transactional
    public DbConnection createConnection(DbConnection connection) {
        // 验证连接名唯一性
        if (dbConnectionMapper.findByUserIdAndName(connection.getUserId(), connection.getName()) != null) {
            throw new RuntimeException("连接名称已存在");
        }
        
        // 测试连接有效性
        testConnection(connection);
        
        // 加密密码
        connection.setPassword(encryptPassword(connection.getPassword()));
        
        // 如果设置为默认，先清除其他默认连接
        if (Boolean.TRUE.equals(connection.getIsDefault())) {
            dbConnectionMapper.clearDefaultForUser(connection.getUserId());
        }
        
        // 保存连接
        dbConnectionMapper.insert(connection);
        return connection.withoutPassword();
    }

    /**
     * 更新数据库连接
     */
    @Transactional
    public DbConnection updateConnection(DbConnection connection, Long userId) {
        DbConnection existing = dbConnectionMapper.findById(connection.getId());
        
        // 验证所有权
        if (existing == null || !existing.getUserId().equals(userId)) {
            throw new RuntimeException("数据库连接不存在或无访问权限");
        }
        
        // 验证连接名唯一性（排除自身）
        DbConnection sameNameConnection = dbConnectionMapper.findByUserIdAndName(userId, connection.getName());
        if (sameNameConnection != null && !sameNameConnection.getId().equals(connection.getId())) {
            throw new RuntimeException("连接名称已存在");
        }
        
        // 测试连接有效性
        testConnection(connection);
        
        // 加密密码
        connection.setPassword(encryptPassword(connection.getPassword()));
        
        // 如果设置为默认，先清除其他默认连接
        if (Boolean.TRUE.equals(connection.getIsDefault())) {
            dbConnectionMapper.clearDefaultForUser(userId);
        }
        
        // 更新连接
        dbConnectionMapper.update(connection);
        return connection.withoutPassword();
    }

    /**
     * 删除数据库连接
     */
    @Transactional
    public void deleteConnection(Long id, Long userId) {
        DbConnection connection = dbConnectionMapper.findById(id);
        
        // 验证所有权
        if (connection == null || !connection.getUserId().equals(userId)) {
            throw new RuntimeException("数据库连接不存在或无访问权限");
        }
        
        // 删除连接
        dbConnectionMapper.deleteById(id);
        
        // 如果删除的是默认连接，尝试设置新的默认连接
        if (Boolean.TRUE.equals(connection.getIsDefault())) {
            List<DbConnection> remainingConnections = dbConnectionMapper.findByUserId(userId);
            if (!remainingConnections.isEmpty()) {
                dbConnectionMapper.setAsDefault(remainingConnections.get(0).getId());
            }
        }
    }
    
    /**
     * 管理员删除数据库连接
     */
    @Transactional
    public void deleteConnectionByAdmin(Long id) {
        DbConnection connection = dbConnectionMapper.findById(id);
        
        if (connection == null) {
            throw new RuntimeException("数据库连接不存在");
        }
        
        // 删除连接
        dbConnectionMapper.deleteById(id);
        
        // 如果删除的是默认连接，尝试设置新的默认连接
        if (Boolean.TRUE.equals(connection.getIsDefault())) {
            List<DbConnection> remainingConnections = dbConnectionMapper.findByUserId(connection.getUserId());
            if (!remainingConnections.isEmpty()) {
                dbConnectionMapper.setAsDefault(remainingConnections.get(0).getId());
            }
        }
    }

    /**
     * 设置默认连接
     */
    @Transactional
    public void setDefaultConnection(Long id, Long userId) {
        DbConnection connection = dbConnectionMapper.findById(id);
        
        // 验证所有权
        if (connection == null || !connection.getUserId().equals(userId)) {
            throw new RuntimeException("数据库连接不存在或无访问权限");
        }
        
        // 清除当前默认
        dbConnectionMapper.clearDefaultForUser(userId);
        
        // 设置新默认
        dbConnectionMapper.setAsDefault(id);
    }

    /**
     * 测试数据库连接
     */
    public void testConnection(DbConnection connection) {
        String jdbcUrl = buildJdbcUrl(connection);
        
        try (Connection conn = getConnection(jdbcUrl, connection.getUsername(), connection.getPassword())) {
            // 连接成功，无需额外操作
            log.info("Successfully tested connection to: {}", jdbcUrl);
        } catch (SQLException e) {
            log.error("Failed to connect to database: {}", jdbcUrl, e);
            throw new RuntimeException("数据库连接测试失败: " + e.getMessage());
        }
    }

    /**
     * 构建JDBC URL
     */
    public String buildJdbcUrl(DbConnection connection) {
        switch (connection.getDbType().toLowerCase()) {
            case "mysql":
                return String.format("jdbc:mysql://%s:%d/%s?useSSL=false&serverTimezone=UTC", 
                        connection.getHost(), connection.getPort(), connection.getDatabaseName());
            case "postgresql":
                return String.format("jdbc:postgresql://%s:%d/%s", 
                        connection.getHost(), connection.getPort(), connection.getDatabaseName());
            case "oracle":
                return String.format("jdbc:oracle:thin:@%s:%d:%s", 
                        connection.getHost(), connection.getPort(), connection.getDatabaseName());
            case "sqlserver":
                return String.format("jdbc:sqlserver://%s:%d;databaseName=%s", 
                        connection.getHost(), connection.getPort(), connection.getDatabaseName());
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + connection.getDbType());
        }
    }

    /**
     * 获取数据库连接
     */
    public Connection getConnection(String jdbcUrl, String username, String password) throws SQLException {
        try {
            // 根据数据库类型加载驱动
            if (jdbcUrl.contains("mysql")) {
                Class.forName("com.mysql.cj.jdbc.Driver");
            } else if (jdbcUrl.contains("postgresql")) {
                Class.forName("org.postgresql.Driver");
            } else if (jdbcUrl.contains("oracle")) {
                Class.forName("oracle.jdbc.OracleDriver");
            } else if (jdbcUrl.contains("sqlserver")) {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            }
        } catch (ClassNotFoundException e) {
            throw new SQLException("数据库驱动加载失败: " + e.getMessage());
        }
        
        return java.sql.DriverManager.getConnection(jdbcUrl, username, password);
    }

    /**
     * 加密密码
     */
    private String encryptPassword(String password) {
        byte[] encryptedBytes = encryptor.encrypt(password.getBytes());
        return Base64.getEncoder().encodeToString(encryptedBytes);
    }

    /**
     * 解密密码
     */
    private String decryptPassword(String encryptedPassword) {
        try {
            byte[] encryptedBytes = Base64.getDecoder().decode(encryptedPassword);
            byte[] decryptedBytes = encryptor.decrypt(encryptedBytes);
            return new String(decryptedBytes);
        } catch (Exception e) {
            log.error("密码解密失败", e);
            throw new RuntimeException("密码解密失败");
        }
    }
} 